﻿	SELECT 
	REGION_DESC_U
	,MARKET_AREA_U
	,STATE_U
	,AM_U
	,AE_ASSIGNED_U
	,PORTFOLIO_NAME_U
	,PROPERTY_ID_U
	,PROPERTY_NAME_U
	,NATIONAL_IND_U
	,DWELLING_TYPE_U
	,POSTDATE_U
	,PRODUCT_ID_U
	,PRODUCT_ACTION_CD_U  
	,PROD_QTY_U
	 FROM
	( -- TOT 		
		SELECT  
		REGION AS REGION_DESC_U
		,MARKET_AREA  AS MARKET_AREA_U
		,AM  AS AM_U
		,AE_ASSIGNED  AS AE_ASSIGNED_U
		,PORTFOLIO_NAME  AS PORTFOLIO_NAME_U
		,STATE AS STATE_U
		,LEGS.PROPERTY_ID AS PROPERTY_ID_U
		,PROPERTY_NAME  AS PROPERTY_NAME_U
		,NATIONAL_IND_U
		,DWELLING_TYPE_U
		,POSTDATE  AS POSTDATE_U
		,LEGS.PRODUCT_ID   AS PRODUCT_ID_U
		,PRODUCT_ACTION_CD    AS PRODUCT_ACTION_CD_U  
		,PROD_QTY AS PROD_QTY_U
		FROM
		( -- LEGS			
			SELECT  
			 PROPERTY_ID 
			,REGION
			,PROPERTY_NAME
			,NATIONAL_IND  AS  NATIONAL_IND_U
			,DWELLING_TYPE  AS DWELLING_TYPE_U
			,AM
			,AE_ASSIGNED
			,PORTFOLIO_NAME
			,MARKET_AREA
			,POSTDATE
			,PRODUCT_ID   
			,PRODUCT_ACTION_CD             
			,PROD_QTY  
			,STATE   
			FROM
			( -- CHK
				SELECT 
				S.PROPERTY_ID 
				,SC.REGION
				,SC.PROPERTY_NAME
				,SC.NATIONAL_IND
				,SC.DWELLING_TYPE
				,SC.AM
				,SC.AE_ASSIGNED
				,SC.PORTFOLIO_NAME
				,SC.MARKET_AREA
				,E.POSTDATE
				,E.STATE
				FROM 
				SMARTMOVES.TXSM012_SMOVES_ADDRESSES  
				S
				,
				SMARTMOVES.TXSM101_SO_SCORE 	
				SC
  				,
				( -- E
					SELECT 
					POSTDATE
					,STATE
					FROM
					( -- BASE
						SELECT	
						POSTDATE
						,C.TERRITORY_CD AS STATE
						FROM  
						ENTERPRISE_RETAIL_VIEWS.VCCG559_ACCOUNT_CNTCT_GEO_ADDR 
						C  
						,
						( -- S	
							SELECT 
							H205.EVENT_POSTED_DT    			POSTDATE
							FROM
							ENTERPRISE_RETAIL_VIEWS.VCCH205_SERVICE_ORDER_EVENT 
							H205
						) 
						S
						WHERE 
						C.REPOSITORY_AFFILIATE_ACCT_ID = S.RAAID
						AND C.ZIP_CD <> ' '
						AND C.ADDRESS_USAGE_CD = 'S' 
					) 
					BASE
					GROUP BY 1,2
				) 
				E
				WHERE 
				TRIM(S.ADDRESS) = TRIM(E.ADDRESS)
				AND S.ZIP = E.ZIP_CD
				AND S.PROPERTY_ID = SC.PROPERTY_ID
				AND (SC.PR_STATUS = 'ACTIVE' AND C_STATUS = 'ACTIVE' ) 
			) 
			CHK
			LEFT OUTER JOIN 
			( -- ACT				
				SELECT 
				 D.PRODUCT_ID                    
				,CASE WHEN INWARD_OUTWARD_CD = 1 THEN 'I' ELSE 'O' END AS PRODUCT_ACTION_CD             
				,SUM(D.PRODUCT_QTY)    PROD_QTY                  
				FROM 
				ENTERPRISE_RETAIL_VIEWS.VCCH207_SVC_ORD_PROD_EVENT  
				D
				WHERE  
				D.DATA_SOURCE_CD IN (150)
				AND D.INWARD_OUTWARD_CD  IN (1,4)
				AND D.PRODUCT_ID > 0
				GROUP BY 1,2,3
			) 
			ACT
			ON CHK.EVENT_ID = ACT.EVENT_ID
		) 
		LEGS
		WHERE PRODUCT_ACTION_CD IN ('I','O')
		GROUP BY 1,2,3,4,5,6,7,8,
	) 
	TOT
/*  ADDED PER  MIST  131152: Determine/Develop Process for CLLI-DA Tracking for ACC- Uverse    */
	GROUP BY 1,2,3,4,5,6,7,8
/*  UVERSE ORDERS 
1/19/2010  CHANGED SPECIAL_TABLES.DG8331 TO 
SMARTMOVES.TXSM101_SO_SCORE AND DIST_CHANNEL SOURCE
TO CHANNEL FROM  CHANNEL_PARTNER   */
